﻿#encoding: utf-8


# 功能【221118-1】 用于将用户提交的答卷内容归档
# Usage: ruby survey-collector.rb

require 'securerandom'
require 'open-uri'

load 'tutem_utils.rb'
load 'wechat_api.rb'

def save_url_to_file url, fn
    begin
      open(fn, 'wb') do |file|
        file << open(url).read
      end
    rescue => e
        err_code = e.message.split(' ')
        err = err_code[0].to_i
        #puts "[FAILED] #{e.message} - #{url}"
        return err if err > 0 
        return 839
    end
    return 200
end

#下载远程文件到本地服务器，并返回本地服务器的访问链接地址
def save_url_to_named_file url, named = Time.now.to_date.to_s

    missing = 'missing.png'
    #url = 'http://oms2.totembio.cn/uploads/a.png'
    short_url = (url.split('?sign='))[0]

    words = short_url.split('.')
    suffix = words[words.size-1]
    uuid = SecureRandom.uuid
    fn = "#{named}-#{uuid}.#{suffix}"
    fp = "nginx/html/uploads/#{fn}"

    res = save_url_to_file url, fp

    link = res == 200 ? "http://oms2.totembio.cn/uploads/#{fn}" : "http://oms.totembio.cn/uploads/#{res}.png"
    return link

end
# puts save_url_to_named_file "http://oms2.totembio.cn/uploads/a.png"

def get_answer_by_id answers, id
    answers.each do |answer|
        return answer if answer['id'].include? id #允许模糊匹配
    end
    return nil
end

def get_texted_answer_by_qid id

    if @the_questions.nil?
        puts 'set @the_answers before use'
        exit
    end

    @the_questions.each do |answer|
        if answer['id'].include? id #允许模糊匹配
            case answer['type']
            when 'datetime', 'text', 'textarea'
                return answer['text']
            when 'radio'
                return answer['options'][0]['text']
            when 'checkbox'
                options = answer['options']
                opt_idx = 0
                text = ''
                options.each do |opt|
                    text += "、" if opt_idx != 0
                    text += opt['text']
                    opt_idx += 1
                end
                return text
            when 'upload'
                files = answer['files']
                f_idx = 0
                text = ''
                files.each do |f|
                    text += "、" if f_idx != 0
                    link = save_url_to_named_file f['url']
                    text += link
                    f_idx += 1
                end
                return text
            else
                return answer.to_s
            end
        end
    end
    return ''
end

#将program_id==6的payload翻译为格式化答案
#未知的格式返回空值
def parse_payload payload, survey_id
    return parse_payload_for_10814373 payload if survey_id == '10814373'
    return parse_payload_for_10672675 payload if survey_id == '10672675'
    return parse_payload_for_10678271 payload if survey_id == '10678271'
    return parse_payload_for_10807096 payload if survey_id == '10807096'
    return parse_payload_for_11205068 payload if survey_id == '11205068'
    return nil
end


#产品退市内部需求及建议征集
def parse_payload_for_11205068 payload

    result = {}

    questions = payload['answer'][0]['questions']
    @the_questions = questions

    answers = "\n"
    answers += "[" + payload['ended_at'] + "] 用户#" + payload['respondent_id'] + "通过" +  payload['respondent_type']
    answers += "提交于" + payload['country'] + payload['province'] + payload['city'] + "\n"

    value =  get_texted_answer_by_qid('q-3-ufnu'); answers += "主要需求：" + value + "\n"
    value =  get_texted_answer_by_qid('q-2-wszn'); answers += "关注重点：" + value + "\n"
    value =  get_texted_answer_by_qid('q-3-M1Wy'); answers += "建议意见：" + value + "\n"
    value =  get_texted_answer_by_qid('q-5-vvt6'); answers += "上传附件：" + value + "\n"

    result.store 'answers', answers

    user = {}
    result.store 'user', user

    return result

end

#图腾之友登记计划第III版
def parse_payload_for_10807096 payload

    result = {}

    questions = payload['answer'][0]['questions']
    @the_questions = questions

    answers = "[#{Time.now.strftime('%Y-%m-%d %H:%M:%S')}] OMS PARSED\n"
    answers += "[" + payload['ended_at'] + "] 用户#" + payload['openid'] + "提交于" + payload['country'] + payload['province'] + payload['city'] + "\n"

    value =  get_texted_answer_by_qid('q-2-'); answers += "登记姓名：" + value + "；"
    value =  get_texted_answer_by_qid('q-14-xB6m'); answers += "登记手机：" + value + "；"
    value =  get_texted_answer_by_qid('q-8-Mp77'); answers += "了解渠道：" + value + "。\n"

    value =  get_texted_answer_by_qid('q-3-jhL8'); answers += "使用者：" + value + "；"
    value =  get_texted_answer_by_qid('q-3-2c74'); answers += "性别：" + value + "；"
    value =  get_texted_answer_by_qid('q-5-40dK'); answers += "出生日期：" + value + "。\n"

    value =  get_texted_answer_by_qid('q-6-wcnH'); answers += "计划申请项目补贴购买：" + value + "；"
    value =  get_texted_answer_by_qid('q-7-YgE1'); answers += "每日用量：" + value + "；"
    value =  get_texted_answer_by_qid('q-7-ikV0'); answers += "用于：" + value + "；"
    value =  get_texted_answer_by_qid('q-10-HLbE'); answers += "最早起病时间：" + value + "；"
    value =  get_texted_answer_by_qid('q-11-4XQs'); answers += "诊断：" + value + "；"
    value =  get_texted_answer_by_qid('q-13-3Dgc'); answers += "诊断文件：" + value + "；"
    value =  get_texted_answer_by_qid('q-11-mBA0'); answers += "基线治疗方案：" + value + "。\n"

    value =  get_texted_answer_by_qid('q-15-SMD0'); answers += "清楚调理特点：" + value + "；"
    value =  get_texted_answer_by_qid('q-15-1VKf'); answers += "同意项目规则：" + value + "。\n"

    result.store 'answers', answers

    user = {}
    result.store 'user', user

    return result

end

#图腾之友登记计划第II版
def parse_payload_for_10678271 payload

    result = {}

    questions = payload['answer'][0]['questions']
    @the_questions = questions

    answers = "[#{Time.now.strftime('%Y-%m-%d %H:%M:%S')}] OMS PARSED\n"
    answers += "[" + payload['ended_at'] + "] 用户#" + payload['openid'] + "提交于" + payload['country'] + payload['province'] + payload['city'] + "\n"

    value =  get_texted_answer_by_qid('q-10-'); answers += "登记姓名：" + value + "；"
    value =  get_texted_answer_by_qid('q-9-'); answers += "登记手机：" + value + "；"
    value =  get_texted_answer_by_qid('q-13-xSOG'); answers += "了解渠道：" + value + "。\n"

    value =  get_texted_answer_by_qid('q-2-'); answers += "使用者：" + value + "；"
    value =  get_texted_answer_by_qid('q-3-'); answers += "性别：" + value + "；"
    value =  get_texted_answer_by_qid('q-11-KaQ0'); answers += "出生日期：" + value + "。\n"

    value =  get_texted_answer_by_qid('q-13-RA'); answers += "计划申请项目补贴购买：" + value + "；"
    value =  get_texted_answer_by_qid('q-14-Ed6L'); answers += "每日用量：" + value + "；"
    value =  get_texted_answer_by_qid('q-13-BG'); answers += "用于：" + value + "；"
    value =  get_texted_answer_by_qid('q-6-'); answers += "最早起病时间：" + value + "；"
    value =  get_texted_answer_by_qid('q-11-8spJ'); answers += "诊断：" + value + "；"
    value =  get_texted_answer_by_qid('q-8-sOYd'); answers += "诊断文件：" + value + "；"
    value =  get_texted_answer_by_qid('q-13-7Pdr'); answers += "基线治疗方案：" + value + "。\n"

    value =  get_texted_answer_by_qid('q-16-LcNM'); answers += "清楚调理特点：" + value + "；"
    value =  get_texted_answer_by_qid('q-12-FBsP'); answers += "同意项目规则：" + value + "。\n"

    result.store 'answers', answers

    user = {}
    result.store 'user', user

    return result

end

#专业用户注册登记第II版
def parse_payload_for_10672675 payload

    result = {}
    user = {}

    questions = payload['answer'][0]['questions']

    answers = "[#{Time.now.strftime('%Y-%m-%d %H:%M:%S')}] OMS PARSED\n"
    answers += "[" + payload['ended_at'] + "] 用户#" + payload['openid'] + "提交于" + payload['country'] + payload['province'] + payload['city'] + "\n"

    name = questions[0]['text']
    answers += "姓名：" + name  + "，"
    user.store 'real_name', name

    agency = questions[2]['text']
    answers += "单位：" + agency + "，"
    user.store 'agency', agency

    department = questions[3]['text']
    answers += "科室：" + department + "，"
    user.store 'department', department

    phone = questions[6]['text']
    answers += "电话: " + phone + "，"
    user.store 'phone', phone

    cert = questions[1]['text']
    answers += "证件: " + cert + "，"
    user.store 'cert', cert

    addr = "无"
    begin
        addr =  questions[7]['text']
    rescue => e
    end
    answers += "地址: " + addr + "。"
    user.store 'addr', addr

    answers += "\n"

    asw = questions[4]['options'][0]['text'].to_s
    text = asw
    text = '已经有' if asw == '是'
    text = '还没有' if asw == '否'
    answers += "目前" + text + "用过相关产品，"
    answers += "感兴趣使用的产品包括："
    options = questions[5]['options']
    opt_idx = 0
    options.each do |opt|
        answers += "、" if opt_idx != 0
        answers += opt['text']
        opt_idx += 1
    end
    answers += "。\n"

    user.store 'event', "[" + payload['ended_at'] + "] 用户申请专业会员：收货地址：#{addr}"

    result.store 'user', user
    result.store 'answers', answers

    return result

end


def gen_usql_for_user uid, new_user_info

    return '' if new_user_info.nil? || new_user_info == {}
    isql = "select uid, real_name, title as title, agency, phone, cert, events from tutem_mall.eb_user where uid = #{uid}"
    existed = {}
    (queryRds(isql)).each do |i|
        existed = i
    end
    return '' if existed == {}

    new_name = new_user_info['real_name'].to_s
    use_new_name = existed['real_name'].to_s == '' && new_name != '' ? "real_name = '#{@rds.escape new_name}'," : ''
    new_title = new_user_info['title'].to_s
    use_new_title = existed['title'].to_s == '' && new_title != '' ? "title = '#{@rds.escape new_title}'," : ''
    new_agency = new_user_info['agency'].to_s
    use_new_agency = existed['agency'].to_s == '' && new_agency != '' ? "agency = '#{@rds.escape new_agency}'," : ''
    new_department = new_user_info['department'].to_s
    use_new_department = existed['department'].to_s == '' && new_department != '' ? "department = '#{@rds.escape new_department}'," : ''
    new_phone = new_user_info['phone'].to_s
    use_new_phone = existed['phone'].to_s == '' && new_phone != '' ? "phone = '#{@rds.escape new_phone}'," : ''
    new_cert = new_user_info['cert'].to_s
    use_new_cert = existed['cert'].to_s == '' && new_cert != '' ? "cert = '#{@rds.escape new_cert}'," : ''
    new_event = new_user_info['event'].to_s

    usql = "update tutem_mall.eb_user set #{use_new_name} #{use_new_title} #{use_new_agency} #{use_new_department} #{use_new_phone} #{use_new_cert} events = '#{@rds.escape new_event}' where uid = #{uid}"
    return usql

end


#专业用户注册登记第III版
def parse_payload_for_10814373 payload

    result = {}
    user = {}

    questions = payload['answer'][0]['questions']

    answers = "[#{Time.now.strftime('%Y-%m-%d %H:%M:%S')}] OMS PARSED\n"
    answers += "[" + payload['ended_at'] + "] 用户#" + payload['openid'] + "提交于" + payload['country'] + payload['province'] + payload['city'] + "\n"

    name = questions[3]['text']
    answers += "姓名：" + name  + "，"
    user.store 'real_name', name

    title = questions[4]['options'][0]['text']
    answers += "职称：" + title + "，"
    user.store 'title', title

    agency = questions[5]['text']
    answers += "单位：" + agency + "，"
    user.store 'agency', agency

    phone = questions[7]['text']
    answers += "电话: " + phone + "，"
    user.store 'phone', phone

    cert = questions[8]['text']
    answers += "证件: " + cert + "，"
    user.store 'cert', cert

    addr = "无"
    begin
        addr =  questions[9]['text']
    rescue => e
    end
    answers += "地址: " + addr + "。"
    user.store 'addr', addr

    answers += "\n"


    answers += "经" + questions[0]['options'][0]['text'] + "后得知图腾产品，"
    asw = questions[1]['options'][0]['text'].to_s
    text = asw
    text = '已经有' if asw == '是'
    text = '还没有' if asw == '否'
    answers += "目前" + text + "用过相关产品，"
    answers += "感兴趣使用的领域包括："
    options = questions[2]['options']
    opt_idx = 0
    options.each do |opt|
        answers += "、" if opt_idx != 0
        answers += opt['text']
        opt_idx += 1
    end
    answers += "。\n"

    ask = '无'
    begin
        ask =  questions[6]['text']
    rescue => e
    end
    answers += "首要关心: " + ask + "。"
    user.store 'event', "[" + payload['ended_at'] + "] 用户申请专业会员：收货地址：#{addr}；关心问题：#{ask}"

    answers += "\n"

    result.store 'user', user
    result.store 'answers', answers

    return result

end

def gen_usql_for_user uid, new_user_info

    return '' if new_user_info.nil? || new_user_info == {}
    isql = "select uid, real_name, title as title, agency, phone, cert, events from tutem_mall.eb_user where uid = #{uid}"
    existed = {}
    (queryRds(isql)).each do |i|
        existed = i
    end
    return '' if existed == {}

    new_name = new_user_info['real_name'].to_s
    use_new_name = existed['real_name'].to_s == '' && new_name != '' ? "real_name = '#{@rds.escape new_name}'," : ''
    new_title = new_user_info['title'].to_s
    use_new_title = existed['title'].to_s == '' && new_title != '' ? "title = '#{@rds.escape new_title}'," : ''
    new_agency = new_user_info['agency'].to_s
    use_new_agency = existed['agency'].to_s == '' && new_agency != '' ? "agency = '#{@rds.escape new_agency}'," : ''
    new_department = new_user_info['department'].to_s
    use_new_department = existed['department'].to_s == '' && new_department != '' ? "department = '#{@rds.escape new_department}'," : ''
    new_phone = new_user_info['phone'].to_s
    use_new_phone = existed['phone'].to_s == '' && new_phone != '' ? "phone = '#{@rds.escape new_phone}'," : ''
    new_cert = new_user_info['cert'].to_s
    use_new_cert = existed['cert'].to_s == '' && new_cert != '' ? "cert = '#{@rds.escape new_cert}'," : ''
    new_event = new_user_info['event'].to_s

    usql = "update tutem_mall.eb_user set #{use_new_name} #{use_new_title} #{use_new_agency} #{use_new_department} #{use_new_phone} #{use_new_cert} events = '#{@rds.escape new_event}' where uid = #{uid}"
    return usql

end

def update_enroll_by_tsid ts_id

    tupalSurvey = get_tupal_survey_by_id ts_id

    uid = tupalSurvey['uid']
    survey_id = tupalSurvey['survey_id']
    action = tupalSurvey['action']
    answers = tupalSurvey['answers']
    submit_time = tupalSurvey['add_time']
    payload = tupalSurvey['payload']

    sql = "select id, status, answers from tutem_mall.eb_tupal_enroll where uid = #{uid} and survey_id = #{survey_id}"
    (queryRds(sql)).each do |q|

        te_id = q['id'] #tupal_enroll_id
        print "already enrolled: uid##{uid} ts_id##{ts_id} survey_id##{survey_id} #{action} ..."
        existed_answers = q['answers'].to_s
        enroll_status = q['status']
        summary = '用户信息已提交，审批或处理反馈将通过图腾生命医学公众号下发'

        # 暂时不区分细分的更新逻辑；例如是update还是create需要更新已存在answers的几种情况,排除多次提交
        his = "[#{Time.now.strftime('%Y-%m-%d %H:%M:%S')}] OMS update answers from ts_id##{ts_id} #{action}"
        puts "updated answers & history for te_id##{te_id}"
        usql = "update tutem_mall.eb_tupal_enroll set answers = '#{@rds.escape answers}', history = concat('#{@rds.escape his}',char(13),history) where id = #{te_id}"
        usql = "update tutem_mall.eb_tupal_enroll set status = 'SUBMITTED', submit_time = #{submit_time},
                payload = '#{@rds.escape payload}', answers = '#{@rds.escape answers}', summary = '#{@rds.escape summary}',
                history = concat('#{@rds.escape his}',char(13),history) where id = #{te_id}" if enroll_status == 'DRAFT'
        queryRds usql
        #puts "[FAKE QUERY] " + usql

        #这里直接返回意味着会忽略所有重复的同一uid+survey_id的纪录处理
        return te_id

    end

    #在enroll表中没有相应纪录
    puts "not enrolled: uid##{uid} ts_id##{ts_id} survey_id##{survey_id} #{action} ..."
    begin
        te_id = create_tupal_enroll_from_survey tupalSurvey
    rescue=>e
        puts "skipped ts_id #{ts_id}"
        te_id = 0
    end

    return te_id

end

def get_tupal_survey_by_id ts_id
    isql = "select ts.*, sl.survey_hash, sl.program_id, eu.real_name as user_name, tp.description as program_description, tp.code as program_code from tutem_mall.eb_tupal_survey ts left join tutem_mall.eb_survey_list sl on (ts.survey_id = sl.survey_id) left join tutem_mall.eb_user eu on (ts.uid = eu.uid) left join tutem_mall.eb_tupal_program tp on (sl.program_id = tp.id) where ts.id = #{ts_id};"
    tupal_survey = {}
    (queryRds(isql)).each do |i|
        tupal_survey = i
        break
    end
    return tupal_survey
end

#根据tupal_survey的纪录，补充tupal_enroll中的数据，返回新创建的tupal_enroll纪录id
def create_tupal_enroll_from_survey tupal_survey

    return 0 if tupal_survey == {}

    ts_id = tupal_survey['id']
    uid = tupal_survey['uid']
    survey_hash = tupal_survey['survey_hash']
    survey_id = tupal_survey['survey_id']
    payload = tupal_survey['payload']
    answers = tupal_survey['answers'].to_s
    program_id = tupal_survey['program_id']
    user_name = tupal_survey['user_name']
    program_code = tupal_survey['program_code']
    description = tupal_survey['program_description']
    submit_time = tupal_survey['add_time']
    title = "#{user_name}的#{program_code}计划问卷"
    now = Time.now
    his = "[#{now.strftime('%Y-%m-%d %H:%M:%S')}] OMS create enroll & update answers from ts_id##{ts_id}"

    csql = "INSERT INTO tutem_mall.eb_tupal_enroll (
        uid, program_id, name, status, description, payload, answers, summary,
        add_time, submit_time, close_time, comment, survey_id, survey_hash, history ) VALUES (
        #{uid}, #{program_id}, '#{title}', 'SUBMITTED', '#{description}', '#{@rds.escape payload}', '#{@rds.escape answers}', '用户信息已提交，审批或处理反馈将通过图腾生命医学公众号下发',
        #{now.to_i}, #{submit_time}, 0, '', '#{survey_id}', '#{survey_hash}', '#{@rds.escape his}');"
    res = @rds.query(csql)
    #puts "[FAKE QUERY] " + csql

    return @rds.last_id

end

def parse_answers_for_survey_in ids_selection = "select id from tutem_mall.eb_tupal_survey where ( action = 'answer.create' or action = 'answer.update' ) and sync = 0"

    sql = "select s.id, s.uid, s.action, s.payload, s.survey_id, l.sync_table, l.title as survey_name from tutem_mall.eb_tupal_survey s left join tutem_mall.eb_survey_list l on (s.survey_id = l.survey_id)"
    sql += " where s.id in (#{ids_selection}) order by id desc" if ids_selection.include? 'select'

    puts "parsing & updating answers for surveys in selected recs..."
    cnt = 0

    (queryRds(sql)).each do |q|

        ts_id = q['id']
        action = q['action']
        uid = q['uid']
        survey_id = q['survey_id']
        text = q['payload'].gsub("\r","").gsub("\n","\\n")
        payload = JSON.parse text #遇到中间由换行符的临时处理
        sync_table = q['sync_table']
        survey_name = q['survey_name']

        result = parse_payload payload, survey_id

        #当用于调试新解析函数时将not_drill设为false，同时在对应函数中打印结果即可
        not_drill = true #DEBUG

        #只处理能够解析结构的纪录 - 演习模式，也可以在里面灵活的处理和答应结果
        if !not_drill && !result.nil?
            cnt += 1
            puts "[DRILL MODE] 收到一份针对「#{survey_name}」的答卷：#{result['answers']}" #if cnt == 1
            exit if cnt == 1
        end

        #只处理能够解析结构的纪录 - 生产模式
        if !result.nil? && not_drill
            cnt += 1
            puts "ts_id##{ts_id} successfully parsed"
            answers = result['answers']
            puts answers
            new_user_info = result['user']

            #如果调查表中有系统用户表中不知道的用户信息，调查表数据予以更新
            usql = gen_usql_for_user uid, new_user_info
            queryRds usql if !usql.empty?
            #puts "[FAKE QUERY] " + usql

            #先更新survey中的answers，因为后面更新enroll或创造时要从纪录中读取
            usql = "update tutem_mall.eb_tupal_survey set answers = '#{@rds.escape answers}' where id = #{ts_id}" #if uid == 1 #test
            queryRds usql
            #puts "[FAKE QUERY] " + usql

            ww_notice = "收到一份针对「#{survey_name}」的答卷：#{answers}"
            send_message_to_bot 'WWBOT_TT', ww_notice, ['@all']

            case sync_table
            when 'eb_tupal_enroll'
                te_id = update_enroll_by_tsid ts_id #返回对应的eb_tupal_enroll纪录id
                #更新关联的的eb_tupal_enroll数据,如果未在enroll中创建则应予以创建，当然必须是报名登记表
                usql = "update tutem_mall.eb_tupal_survey set sync = #{te_id} where id = #{ts_id}" #if uid == 1 #test
                queryRds usql
                #puts "[FAKE QUERY] " + usql
            else
                usql = "update tutem_mall.eb_tupal_survey set sync = -1 where id = #{ts_id}"
                queryRds usql
                puts "[FAKE QUERY] " + usql
            end

        end

    end

    puts "done[#{cnt}]"

end

#2022-11-20-0 测试uid==1, 处理完的都应该打上了sync标记
#usqls = parse_answers_for_survey_in "select id from tutem_mall.eb_tupal_survey where ( action = 'answer.create' or action = 'answer.update' ) and sync = 0 and uid = 1"
#2022-11-20-1 更新所有10814373问卷提交的数据
parse_answers_for_survey_in "select id from tutem_mall.eb_tupal_survey where ( action = 'answer.create' or action = 'answer.update' ) and sync = 0"
